﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;

namespace WebKpiPage.Data.Common
{
    public sealed class DataUtil
    {
        private DataUtil()
        {
            // All static methods
        }
        #region ConvertDatareaderToDataSet
        /// <summary>
        /// Converts a IDataReader to a DataSet.  For use when a custom stored procedure returns an <see cref="IDataReader" />, it will 
        /// convert all result sets returned as a DataSet.
        /// </summary>
        /// <param name="reader">The reader to convert</param>
        /// <returns>A dataset with one table per result in the reader</returns>
        public static DataSet ConvertDataReaderToDataSet(IDataReader reader)
        {
            DataSet dataSet = new DataSet();
            do
            {
                // Create new data table

                DataTable schemaTable = reader.GetSchemaTable();
                DataTable dataTable = new DataTable();

                if (schemaTable != null)
                {
                    // A query returning records was executed

                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        DataRow dataRow = schemaTable.Rows[i];
                        // Create a column name that is unique in the data table
                        string columnName = (string)dataRow["ColumnName"];
                        // Add the column definition to the data table
                        DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
                        dataTable.Columns.Add(column);
                    }

                    dataSet.Tables.Add(dataTable);

                    // Fill the data table we just created

                    while (reader.Read())
                    {
                        DataRow dataRow = dataTable.NewRow();

                        for (int i = 0; i < reader.FieldCount; i++)
                            dataRow[i] = reader.GetValue(i);

                        dataTable.Rows.Add(dataRow);
                    }
                }
                else
                {
                    // No records were returned

                    DataColumn column = new DataColumn("RowsAffected");
                    dataTable.Columns.Add(column);
                    dataSet.Tables.Add(dataTable);
                    DataRow dataRow = dataTable.NewRow();
                    dataRow[0] = reader.RecordsAffected;
                    dataTable.Rows.Add(dataRow);
                }
            }
            while (reader.NextResult());
            return dataSet;
        }
        #endregion

        #region ConnectionString
        public static SqlConnection MakeSqlConnection(string ds, string ui, string pw)
        {
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
            csb.DataSource = ds;
            csb.UserID = ui;
            csb.Password = pw;
            return new SqlConnection(csb.ConnectionString);
        }
        #endregion

        #region ExecuteReader
        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="conn">The specified SqlConnection.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <param name="single_row">Only the first row if true.</param>
        /// <returns></returns>
        public static IDataReader ExecuteReader(SqlConnection conn, CommandType commandType, string commandText, bool single_row)
        {
            lock (conn)
            {
                SqlCommand scmd = conn.CreateCommand();
                scmd.CommandText = commandText;
                scmd.CommandType = commandType;
                try
                {
                    return scmd.ExecuteReader(single_row ? CommandBehavior.SingleRow : CommandBehavior.Default);
                }
                catch
                {
                    scmd.Cancel();
                    throw;
                }
            }
        }

        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="conn">The specified SqlConnection.</param>
        /// <param name="commandText">The command text.</param>
        /// <param name="single_row">Only the first row if true.</param>
        /// <returns></returns>
        public static IDataReader ExecuteReader(SqlConnection conn, string commandText, bool single_row)
        {
            lock (conn)
            {
                SqlCommand scmd = conn.CreateCommand();
                scmd.CommandText = commandText;
                scmd.CommandType = CommandType.Text;
                try
                {
                    return scmd.ExecuteReader(single_row ? CommandBehavior.SingleRow : CommandBehavior.Default);
                }
                catch
                {
                    scmd.Cancel();
                    throw;
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="single_row"></param>
        /// <returns></returns>
        public static IDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, bool single_row)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && transaction.Connection == null)
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            }

            SqlConnection conn = transaction.Connection;
            lock (conn)
            {
                SqlCommand scmd = conn.CreateCommand();
                scmd.CommandText = commandText;
                scmd.CommandType = commandType;
                scmd.Transaction = transaction;
                try
                {
                    return scmd.ExecuteReader(single_row ? CommandBehavior.SingleRow : CommandBehavior.Default);
                }
                catch
                {
                    scmd.Cancel();
                    throw;
                }
            }
        }

        public static IDataReader ExecuteReader(SqlTransaction transaction, string commandText, bool single_row)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && transaction.Connection == null)
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            }

            SqlConnection conn = transaction.Connection;
            lock (conn)
            {
                SqlCommand scmd = conn.CreateCommand();
                scmd.CommandText = commandText;
                scmd.CommandType = CommandType.Text;
                scmd.Transaction = transaction;
                try
                {
                    return scmd.ExecuteReader(single_row ? CommandBehavior.SingleRow : CommandBehavior.Default);
                }
                catch
                {
                    scmd.Cancel();
                    throw;
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static DataTable LoadDataTable(SqlConnection conn, string commandText)
        {
            DataTable dt = new DataTable();
            lock (conn)
            {
                try
                {
                    SqlDataAdapter sda = new SqlDataAdapter(commandText, conn);
                    sda.Fill(dt);
                }
                catch (Exception)
                {
                    conn.Close();
                    throw;
                }
            }
            return dt;
        }
        #endregion

        #region ExecuteNonQuery
        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="conn">The specified SqlConnection.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(SqlConnection conn, CommandType commandType, string commandText)
        {
            lock (conn)
            {
                SqlCommand scmd = conn.CreateCommand();
                scmd.CommandText = commandText;
                scmd.CommandType = commandType;
                try
                {
                    return scmd.ExecuteNonQuery();
                }
                catch
                {
                    scmd.Cancel();
                    throw;
                }
            }
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="conn">The specified SqlConnection.</param>
        /// <param name="commandText">The command text.</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(SqlConnection conn, string commandText)
        {
            lock (conn)
            {
                SqlCommand scmd = conn.CreateCommand();
                scmd.CommandText = commandText;
                scmd.CommandType = CommandType.Text;
                try
                {
                    return scmd.ExecuteNonQuery();
                }
                catch
                {
                    scmd.Cancel();
                    throw;
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && transaction.Connection == null)
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            }

            SqlConnection conn = transaction.Connection;
            lock (conn)
            {
                SqlCommand scmd = conn.CreateCommand();
                scmd.CommandText = commandText;
                scmd.CommandType = commandType;
                scmd.Transaction = transaction;
                try
                {
                    return scmd.ExecuteNonQuery();
                }
                catch
                {
                    scmd.Cancel();
                    throw;
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(SqlTransaction transaction, string commandText)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && transaction.Connection == null)
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            }

            SqlConnection conn = transaction.Connection;
            lock (conn)
            {
                SqlCommand scmd = conn.CreateCommand();
                scmd.CommandText = commandText;
                scmd.CommandType = CommandType.Text;
                scmd.Transaction = transaction;
                try
                {
                    return scmd.ExecuteNonQuery();
                }
                catch
                {
                    scmd.Cancel();
                    throw;
                }
            }
        }
        #endregion

        #region ExecuteDataAdapter
        public static SqlDataAdapter ExecuteDataAdapter(SqlConnection conn, CommandType commandType, string commandText)
        {
            SqlDataAdapter adp;
            lock (conn)
            {
                SqlCommand scmd = conn.CreateCommand();
                scmd.CommandText = commandText;
                scmd.CommandType = commandType;
                adp = new SqlDataAdapter(scmd);
            }
            return adp;
        }

        public static SqlDataAdapter ExecuteDataAdapter(SqlConnection conn, string commandText)
        {
            SqlDataAdapter adp;
            lock (conn)
            {
                adp = new SqlDataAdapter(commandText, conn);
            }
            return adp;
        }

        #endregion

        #region ExecuteDataSet
        /// <summary>
        /// Executes the data set.
        /// </summary>
        /// <param name="conn">The specified SqlConnection.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(SqlConnection conn, CommandType commandType, string commandText)
        {
            DataSet rst = new DataSet();
            SqlDataAdapter adp = ExecuteDataAdapter(conn, commandType, commandText);
            adp.Fill(rst);
            return rst;
        }

        /// <summary>
        /// Executes the data set.
        /// </summary>
        /// <param name="conn">The specified SqlConnection.</param>
        /// <param name="commandText">The command text.</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(SqlConnection conn, string commandText)
        {
            DataSet rst = new DataSet();
            SqlDataAdapter adp = ExecuteDataAdapter(conn, commandText);
            adp.Fill(rst);
            return rst;
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="conn">The specified SqlConnection.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="commandText">The command text.</param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlConnection conn, CommandType commandType, string commandText)
        {
            lock (conn)
            {
                SqlCommand scmd = conn.CreateCommand();
                scmd.CommandText = commandText;
                scmd.CommandType = commandType;
                try
                {
                    return scmd.ExecuteScalar();
                }
                catch
                {
                    scmd.Cancel();
                    throw;
                }
            }
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="conn">The specified SqlConnection.</param>
        /// <param name="commandText">The command text.</param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlConnection conn, string commandText)
        {
            lock (conn)
            {
                SqlCommand scmd = conn.CreateCommand();
                scmd.CommandText = commandText;
                scmd.CommandType = CommandType.Text;
                try
                {
                    return scmd.ExecuteScalar();
                }
                catch
                {
                    scmd.Cancel();
                    throw;
                }
            }
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && transaction.Connection == null)
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            }

            SqlConnection conn = transaction.Connection;
            lock (conn)
            {
                SqlCommand scmd = conn.CreateCommand();
                scmd.CommandText = commandText;
                scmd.CommandType = commandType;
                scmd.Transaction = transaction;
                try
                {
                    return scmd.ExecuteScalar();
                }
                catch
                {
                    scmd.Cancel();
                    throw;
                }
            }
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static object ExecuteScalar(SqlTransaction transaction, string commandText)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException("transaction");
            }
            if (transaction != null && transaction.Connection == null)
            {
                throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
            }

            SqlConnection conn = transaction.Connection;
            lock (conn)
            {
                SqlCommand scmd = conn.CreateCommand();
                scmd.CommandText = commandText;
                scmd.CommandType = CommandType.Text;
                scmd.Transaction = transaction;
                try
                {
                    return scmd.ExecuteScalar();
                }
                catch
                {
                    scmd.Cancel();
                    throw;
                }
            }
        }
        #endregion
    }
}
